﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace SF.Base.Core {
	/// <summary>
	/// 关系操作类
	/// </summary>
	public class TaxonomyRules {

		#region 新建分类 public static void InsertTaxonomy(TaxonomyEntity term)

		/// <summary>
		/// 新建分类
		/// </summary>
		/// <param name="term"></param>
		public static void Insert(TaxonomyEntity term) {

			IDataParameter[] idps = {
				Sh.GetParameter("@name", DbType.String, term.Name),
				Sh.GetParameter("@slug", DbType.String, term.Slug),
				Sh.GetParameter("@taxonomy", DbType.String, term.Taxonomy),
				Sh.GetParameter("@description", DbType.String, term.Description),
				Sh.GetParameter("@taxonomy_id", DbType.String, term.TaxonomyId),
				Sh.GetParameter("@user_id", DbType.String, term.UserId),
				Sh.GetParameter("@status", DbType.Int32, term.Status),
				Sh.GetParameter("@parent", DbType.String, term.ParentId)
			};

			try {
				Sh.ExecuteNonQuery(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@"
						if not exists ( select 1 from dt_term_taxonomy where [name] = @name and user_id = @user_id and taxonomy = @taxonomy ) 
						begin
							insert into dbo.dt_term_taxonomy ( taxonomy_id,[name],slug,[user_id],taxonomy,[description],parent,status)
							values( @taxonomy_id,@name,@slug,@user_id,@taxonomy,@description,@parent,@status )
						end
						else
						begin
							return -1;
						end
					",
					idps
				);
			}
			catch (System.Exception e) {
				throw new Exception("插入分类失败", e);
			}
		}

		#endregion

		/// <summary>
		/// 添加分类分类
		/// </summary>
		/// <param name="taxonomy">分类对象</param>
		/// <returns></returns>
		public static int AddRelationships(TaxonomyEntity taxonomy){
			try {
				int ret = Sh.ExecuteNonQuery(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@"
						if not exists ( select 1 from dt_term_relationships where object_id = @object_id and taxonomy_id = @taxonomy_id)
						begin
							insert into dt_term_relationships (
								[object_id],taxonomy_id,relation_order,relation_date
							) 
							values(
								@object_id,@taxonomy_id,0,getdate()
							)
						end
						else
						begin
							delete dt_term_relationships where object_id = @object_id and taxonomy_id = @taxonomy_id
						end
					",
					Sh.GetParameter("@object_id", DbType.String, taxonomy.ObjectId),
					Sh.GetParameter("@taxonomy_id", DbType.String, taxonomy.TaxonomyId)
				);
				if (ret == 1) {
					return 0;
				}
				else {
					return -1;
				}
			}
			catch (System.Exception e) {
				throw new Exception("添加分类分类失败", e);
			}
		}

		#region 查询单个分类对象public static TaxonomyEntity GetTaxonomyByTaxonomyId(string taxonomyId)

		/// <summary>
		/// 查询单个分类对象
		/// </summary>
		/// <param name="taxonomyId">分类ID</param>
		/// <returns>分类对象</returns>
		public static TaxonomyEntity GetTaxonomyByTaxonomyId(string taxonomyId) {
			TaxonomyEntity taxonomy = null;

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
						select 
							tid,
							name,
							slug,
							taxonomy_id,
							user_id,
							taxonomy,
							description,
							parent,
							status
						from dt_term_taxonomy 
						where taxonomy_id = @taxonomy_id
					",
						Sh.GetParameter("@taxonomy_id", DbType.String, taxonomyId)
					)
				) {
					if (dr.Read()) {
						taxonomy.TID = dr.GetLong("tid");
						taxonomy.Name = dr.GetStr("name");
						taxonomy.Slug = dr.GetStr("slug");
						taxonomy.Status = dr.GetInt("status");
						taxonomy.TaxonomyId = dr.GetStr("taxonomy_id");
						taxonomy.UserId = dr.GetStr("user_id");
						taxonomy.Taxonomy = dr.GetStr("taxonomy");
						taxonomy.Description = dr.GetStr("description");
						taxonomy.ParentId = dr.GetStr("parent");
					}

					return taxonomy;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询分类目录失败", e);
			}
		}

		/// <summary>
		/// 查询单个分类对象
		/// </summary>
		/// <param name="taxonomyId">分类ID</param>
		/// <returns>分类对象</returns>
		public static TaxonomyEntity GetTaxonomy(string where) {
			TaxonomyEntity taxonomy = null;

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
						select 
							tid,
							name,
							slug,
							taxonomy_id,
							user_id,
							taxonomy,
							description,
							parent,
							status
						from dt_term_taxonomy 
						" + where +
					""
					)
				) {
					if (dr.Read()) {
						taxonomy = new TaxonomyEntity();

						taxonomy.TID = dr.GetLong("tid");
						taxonomy.Name = dr.GetStr("name");
						taxonomy.Slug = dr.GetStr("slug");
						taxonomy.Status = dr.GetInt("status");
						taxonomy.TaxonomyId = dr.GetStr("taxonomy_id");
						taxonomy.UserId = dr.GetStr("user_id");
						taxonomy.Taxonomy = dr.GetStr("taxonomy");
						taxonomy.Description = dr.GetStr("description");
						taxonomy.ParentId = dr.GetStr("parent");
					}

					return taxonomy;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询分类目录失败", e);
			}
		}

		/// <summary>
		/// 查询单个分类对象(从缓存中)
		/// </summary>
		/// <param name="taxonomyId">分类ID</param>
		/// <param name="userId">用户ID</param>
		/// <returns>分类对象</returns>
		public static TaxonomyEntity GetTaxonomyByTaxonomyIdFromCache(string taxonomyId, string userId) {

			List<TaxonomyEntity> lists = CH.GetListCache<TaxonomyEntity>(CH.Keys.TAXONOMYENTITY + userId);

			if (lists == null) {
				lists = GetUserTaxonomysByUserId(userId);
				CH.SetListCache<TaxonomyEntity>(CH.Keys.TAXONOMYENTITY + userId, lists);
			}

			TaxonomyEntity taxonomy = lists.Find(delegate(TaxonomyEntity t) { return t.TaxonomyId == taxonomyId; });

			return taxonomy;
		}

		#endregion

		#region 查询单个分类对象 public static List<TaxonomyEntity> GetUserTaxonomysByUserId(string userId)

		/// <summary>
		/// 查询用户的全部分类
		/// </summary>
		/// <param name="taxonomyId">分类ID</param>
		/// <returns>分类对象</returns>
		public static List<TaxonomyEntity> GetUserTaxonomysByUserId(string userId) {
			List<TaxonomyEntity> taxonomys = new List<TaxonomyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
						select 
							tid,
							name,
							slug,
							status,
							taxonomy_id,
							user_id,
							taxonomy,
							description,
							parent 
						from dt_term_taxonomy
						where user_id = @user_id 
					",
						Sh.GetParameter("@taxonomy_id", DbType.Int32, userId)
					)
				) {
					while (dr.Read()) {
						TaxonomyEntity item = new TaxonomyEntity();

						item.TID = dr.GetLong("tid");
						item.Name = dr.GetStr("name");
						item.Slug = dr.GetStr("slug");
						item.Status = dr.GetInt("status");
						item.TaxonomyId = dr.GetStr("taxonomy_id");
						item.UserId = dr.GetStr("user_id");
						item.Taxonomy = dr.GetStr("taxonomy");
						item.Description = dr.GetStr("description");
						item.ParentId = dr.GetStr("parent");

						taxonomys.Add(item);
					}

					return taxonomys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询用户分类目录失败", e);
			}
		}

		/// <summary>
		/// 查询用户某个特定分类列表
		/// </summary>
		/// <param name="taxonomy">分类</param>
		/// <param name="userId">用户ID</param>
		/// <returns>分类对象</returns>
		public static List<TaxonomyEntity> GetUserTaxonomysByUserId(string userId, string taxonomy) {
			List<TaxonomyEntity> taxonomys = new List<TaxonomyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
						select 
							tid,
							name,
							slug,
							status,
							taxonomy_id,
							user_id,
							taxonomy,
							description,
							parent 
						from dt_term_taxonomy
						where user_id = @user_id 
							and taxonomy = @taxonomy
					",
						Sh.GetParameter("@user_id", DbType.String, userId),
						Sh.GetParameter("@taxonomy", DbType.String, taxonomy)
					)
				) {
					while (dr.Read()) {
						TaxonomyEntity item = new TaxonomyEntity();

						item.TID = dr.GetLong("tid");
						item.Name = dr.GetStr("name");
						item.Slug = dr.GetStr("slug");
						item.Status = dr.GetInt("status");
						item.TaxonomyId = dr.GetStr("taxonomy_id");
						item.UserId = dr.GetStr("user_id");
						item.Taxonomy = dr.GetStr("taxonomy");
						item.Description = dr.GetStr("description");
						item.ParentId = dr.GetStr("parent");

						taxonomys.Add(item);
					}

					return taxonomys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询用户分类目录失败", e);
			}
		}

		#endregion

		#region 删除单个分类 public static void DeleteTaxonomyByTaxonomyId(string taxonomyId,string userId)

		/// <summary>
		/// 删除单个分类
		/// </summary>
		/// <param name="taxonomyId">分类ID</param>
		public static void DeleteTaxonomyByTaxonomyId(string taxonomyId, string userId) {
			try {
				Sh.ExecuteNonQuery(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@"update dt_term_taxonomy set status = -1 where taxonomy_id = @taxonomy_id and user_id = @user_id",
					Sh.GetParameter("@taxonomy_id", DbType.Int32, taxonomyId),
					Sh.GetParameter("@user_id", DbType.String, userId)
				);
			}
			catch (System.Exception e) {
				throw new Exception("删除分类失败", e);
			}
		}

		#endregion

		/// <summary>
		/// 查询用户关注的分类的关系表
		/// </summary>
		/// <param name="userId">用户ID</param>
		/// <param name="taxonomy">分类类型</param>
		/// <returns></returns>
		public static List<TaxonomyEntity> GetSecondLevelTaxonomyByUserId(string userId, string taxonomy) {
			List<TaxonomyEntity> taxonomys = new List<TaxonomyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								a.trid,a.object_id as taxonomy_id,a.relation_order,a.relation_date,
								b.tid,b.name,b.slug,b.description,b.user_id,b.taxonomy,b.parent,b.taxonomy_order,b.taxonomy_date,b.status,
								relation_count = isnull((
									select count(*) from dt_term_relationships c
									where c.taxonomy_id = a.object_id
								),0)
							from dt_term_relationships a left outer join dt_term_taxonomy b
							on a.object_id = b.taxonomy_id
							where a.taxonomy_id in (
								select 
									taxonomy_id 
								from dt_term_taxonomy 
								where taxonomy = @taxonomy 
									and user_id = @user_id
							)
						",
						Sh.GetParameter("@user_id", DbType.String, userId),
						Sh.GetParameter("@taxonomy", DbType.String, taxonomy)
					)
				) {
					while (dr.Read()) {
						TaxonomyEntity item = new TaxonomyEntity();

						item.TID = dr.GetLong("tid");
						item.TaxonomyId = dr.GetStr("taxonomy_id");
						item.Taxonomy = dr.GetStr("taxonomy");
						item.Name = dr.GetStr("name");
						item.Slug = dr.GetStr("slug");
						item.Description = dr.GetStr("description");
						item.Status = dr.GetInt("status");
						item.UserId = dr.GetStr("user_id");
						item.Description = dr.GetStr("description");
						item.ParentId = dr.GetStr("parent");
						item.TaxonomyOrder = dr.GetInt("taxonomy_order");
						item.TaxonomyDate = dr.GetDate("taxonomy_date");
						item.RelationCount = dr.GetInt("relation_count");

						item.TRID = dr.GetLong("trid");
						item.RelationOrder = dr.GetInt("relation_order");
						item.RelationDate = dr.GetDate("relation_date");


						taxonomys.Add(item);
					}

					return taxonomys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询用户关注的分类的关系表失败", e);
			}
		}

		/// <summary>
		/// 查询系统某个特定分类列表
		/// </summary>
		/// <param name="taxonomy">分类</param>
		/// <param name="userId">用户ID</param>
		/// <returns>分类对象</returns>
		public static List<TaxonomyEntity> GetTaxonomysByTaxonomy(string taxonomy, string userId) {
			List<TaxonomyEntity> taxonomys = new List<TaxonomyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								status,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								parent,
								relation_count = isnull((
									select count(*) from dt_term_relationships c where a.taxonomy_id = c.taxonomy_id
								),0)
							from dt_term_taxonomy a
							where taxonomy = @taxonomy
								and user_id = @user_id
						",
						Sh.GetParameter("@taxonomy", DbType.String, taxonomy),
						Sh.GetParameter("@user_id", DbType.String, userId)
					)
				) {
					while (dr.Read()) {
						TaxonomyEntity item = new TaxonomyEntity();

						item.TID = dr.GetLong("tid");
						item.Name = dr.GetStr("name");
						item.Slug = dr.GetStr("slug");
						item.Status = dr.GetInt("status");
						item.TaxonomyId = dr.GetStr("taxonomy_id");
						item.UserId = dr.GetStr("user_id");
						item.Taxonomy = dr.GetStr("taxonomy");
						item.Description = dr.GetStr("description");
						item.ParentId = dr.GetStr("parent");

						item.RelationCount = dr.GetInt("relation_count");

						taxonomys.Add(item);
					}

					return taxonomys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询系统某个特定分类列表失败", e);
			}
		}

		/// <summary>
		/// 查询系统某个特定分类列表
		/// </summary>
		/// <param name="taxonomy">分类</param>
		/// <param name="userId">用户ID</param>
		/// <returns>分类对象</returns>
		public static List<TaxonomyEntity> GetTaxonomysByTaxonomy(string taxonomy) {
			List<TaxonomyEntity> taxonomys = new List<TaxonomyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								status,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								parent,
								relation_count = isnull((
									select count(*) from dt_term_relationships c where a.taxonomy_id = c.taxonomy_id
								),0),
							from dt_term_taxonomy a
							where taxonomy = @taxonomy
						",
						Sh.GetParameter("@taxonomy", DbType.String, taxonomy)
					)
				) {
					while (dr.Read()) {
						TaxonomyEntity item = new TaxonomyEntity();

						item.TID = dr.GetLong("tid");
						item.Name = dr.GetStr("name");
						item.Slug = dr.GetStr("slug");
						item.Status = dr.GetInt("status");
						item.TaxonomyId = dr.GetStr("taxonomy_id");
						item.UserId = dr.GetStr("user_id");
						item.Taxonomy = dr.GetStr("taxonomy");
						item.Description = dr.GetStr("description");
						item.ParentId = dr.GetStr("parent");

						item.RelationCount = dr.GetInt("relation_count");

						taxonomys.Add(item);
					}

					return taxonomys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询系统某个特定分类列表失败", e);
			}
		}


		/// <summary>
		/// 查询分类关系
		/// </summary>
		/// <param name="taxonomy">分类类型</param>
		/// <returns>分类对象</returns>
		public static List<TaxonomyEntity> GetRelationShipsByTaxonomy(string taxonomy, string userId) {
			List<TaxonomyEntity> taxonomys = new List<TaxonomyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								a.tid,
								a.name,
								a.slug,
								a.status,
								a.taxonomy_id,
								a.user_id,
								a.taxonomy,
								a.description,
								a.parent,
								relation_count = isnull((
									select count(*) from dt_term_relationships c where a.taxonomy_id = c.taxonomy_id
								),0),

								b.trid,
								b.object_id,
								b.relation_order,
								b.relation_date
							from dt_term_taxonomy a 
								inner join dt_term_relationships b
									on a.taxonomy_id = b.taxonomy_id
							where taxonomy = @taxonomy
								and user_id = @user_id
						",
						Sh.GetParameter("@taxonomy", DbType.String, taxonomy),
						Sh.GetParameter("@user_id", DbType.String, userId)
					)
				) {
					while (dr.Read()) {
						TaxonomyEntity item = new TaxonomyEntity();

						item.TID = dr.GetLong("tid");
						item.Name = dr.GetStr("name");
						item.Slug = dr.GetStr("slug");
						item.Status = dr.GetInt("status");
						item.TaxonomyId = dr.GetStr("taxonomy_id");
						item.UserId = dr.GetStr("user_id");
						item.Taxonomy = dr.GetStr("taxonomy");
						item.Description = dr.GetStr("description");
						item.ParentId = dr.GetStr("parent");
						item.RelationCount = dr.GetInt("relation_count");

						item.TRID = dr.GetLong("trid");
						item.ObjectId = dr.GetStr("object_id");
						item.RelationOrder = dr.GetInt("relation_order");
						item.RelationDate = dr.GetDate("relation_date");

						taxonomys.Add(item);
					}

					return taxonomys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询分类关系失败", e);
			}
		}

		/// <summary>
		/// 查询与某对象有关系的关系列表
		/// </summary>
		/// <param name="objectId"></param>
		/// <param name="taxonomy"></param>
		/// <returns></returns>
		public static List<TaxonomyEntity> GetRelationShipsByObjectId(string objectId, string taxonomy) {
			List<TaxonomyEntity> taxonomys = new List<TaxonomyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								a.tid,
								a.name,
								a.slug,
								a.status,
								a.taxonomy_id,
								a.user_id,
								a.taxonomy,
								a.description,
								a.parent,
								relation_count = isnull((
									select count(*) from dt_term_relationships c where a.taxonomy_id = c.taxonomy_id
								),0),

								b.trid,
								b.object_id,
								b.relation_order,
								b.relation_date
							from dt_term_taxonomy a inner join dt_term_relationships b
							on a.taxonomy_id = b.taxonomy_id
							where taxonomy = @taxonomy
								and object_id == @object_id
						",
						Sh.GetParameter("@taxonomy", DbType.String, taxonomy),
						Sh.GetParameter("@object_id", DbType.String, objectId)
					)
				) {
					while (dr.Read()) {
						TaxonomyEntity item = new TaxonomyEntity();

						item.TID = dr.GetLong("tid");
						item.Name = dr.GetStr("name");
						item.Slug = dr.GetStr("slug");
						item.Status = dr.GetInt("status");
						item.TaxonomyId = dr.GetStr("taxonomy_id");
						item.UserId = dr.GetStr("user_id");
						item.Taxonomy = dr.GetStr("taxonomy");
						item.Description = dr.GetStr("description");
						item.ParentId = dr.GetStr("parent");
						item.RelationCount = dr.GetInt("relation_count");

						item.TRID = dr.GetLong("trid");
						item.ObjectId = dr.GetStr("object_id");
						item.RelationOrder = dr.GetInt("relation_order");
						item.RelationDate = dr.GetDate("relation_date");

						taxonomys.Add(item);
					}

					return taxonomys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询与某对象有关系的关系列表失败", e);
			}
		}

		/// <summary>
		/// 查询与某对象有关系的用户列表
		/// </summary>
		/// <param name="objectId"></param>
		/// <param name="taxonomy"></param>
		/// <returns></returns>
		public static List<UserEntity> GetUsersFromRelationShipsByObjectId(string objectId, string taxonomy) {
			List<UserEntity> taxonomys = new List<UserEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								uid,user_id,login,pass,nicename,realname,email,url,registered,activationkey,status
							from dt_users 
							where user_id in (
								select 
									a.user_id
								from dt_term_taxonomy a inner join dt_term_relationships b
								on a.taxonomy_id = b.taxonomy_id
								where taxonomy = @taxonomy
									and b.object_id = @object_id
							)
						",
						Sh.GetParameter("@taxonomy", DbType.String, taxonomy),
						Sh.GetParameter("@object_id", DbType.String, objectId)
					)
				) {
					while (dr.Read()) {
						UserEntity item = new UserEntity();

						item.UID = dr.GetLong("uid");
						item.UserId = dr.GetStr("user_id");
						item.Login = dr.GetStr("login");
						item.Pass = dr.GetStr("pass");
						item.NiceName = dr.GetStr("nicename");
						item.RealName = dr.GetStr("realname");

						item.Email = dr.GetStr("email");
						item.Url = dr.GetStr("url");
						item.Registered = dr.GetDate("registered");
						item.ActivationKey = dr.GetStr("activationkey");
						item.Status = dr.GetInt("status");

						taxonomys.Add(item);
					}

					return taxonomys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询与某对象有关系的关系列表失败", e);
			}
		}
	}
}
